//每一对最新的一条在缓存中额外存一份
//totmsg,tosmsg,topmsg,togroupmsg
const myDB = {
	initDB() {
		// this.deleteDB()
		
		let isopen = plus.sqlite.isOpenDatabase({name: 'msgDB',path: '_doc/msg.db'});
		console.log(88, isopen)
		if(!isopen){
			plus.sqlite.openDatabase({
				name: 'msgDB',
				path: '_doc/msg.db',
				success: (e) => {
					this.createTable()
				},
				fail: (e) => {console.log(e)}
			});
		}else {
			this.createTable();
		}
	},
	// 关闭数据库
	closeDB() {
		let isopen = plus.sqlite.isOpenDatabase({name: 'msgDB',path: '_doc/msg.db'});
		if(isopen){
			plus.sqlite.closeDatabase({
				name: 'msgDB',
				success: (e) => {console.log(e)},
				fail: (e) => {console.log(e)}
			});
		}
	},
	//创建表
	createTable(){
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: " create virtual table if not exists totmsg using fts4(sessionId, fromId, to, createTime, msgType,content, extras, isRead, tokenize = icu)",
			fail: (e) => {console.log(e)}
		});
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: " create table if not exists totmsg(" +
		// 		" 'id' INTEGER PRIMARY KEY AUTOINCREMENT ," +
		// 		" 'sessionId' char(13) NOT NULL ," +
		// 		" 'from' char(11) NOT NULL ," +
		// 		" 'to' char(11) NOT NULL ," +
		// 		" 'createTime' bigint NOT NULL ," +
		// 		" 'msgType' SMALLINT NOT NULL ," +
		// 		" 'content' TEXT NOT NULL ," +
		// 		" 'extras' TEXT ," +
		// 		" 'isRead' TINYINT " +
		// 		" )",
		// 	fail: (e) => {console.log(e)}
		// });
		
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: " create virtual table if not exists tosmsg using fts4(sessionId, fromId, to, createTime, msgType,content, extras, isRead, tokenize = icu)",
			fail: (e) => {console.log(e)}
		});
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: " create table if not exists tosmsg(" +
		// 		" 'id' INTEGER PRIMARY KEY AUTOINCREMENT ," +
		// 		" 'sessionId' char(13) NOT NULL ," +
		// 		" 'from' char(11) NOT NULL ," +
		// 		" 'to' char(11) NOT NULL ," +
		// 		" 'createTime' bigint NOT NULL ," +
		// 		" 'msgType' SMALLINT NOT NULL ," +
		// 		" 'content' TEXT NOT NULL ," +
		// 		" 'extras' TEXT ," +
		// 		" 'isRead' TINYINT " +
		// 		" )",
		// 	fail: (e) => {console.log(e)}
		// });
		
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: " create virtual table if not exists topmsg using fts4(sessionId, fromId, to, createTime, msgType,content, extras, isRead, tokenize = icu)",
			fail: (e) => {console.log(e)}
		});
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: " create table if not exists topmsg(" +
		// 		" 'id' INTEGER PRIMARY KEY AUTOINCREMENT ," +
		// 		" 'sessionId' char(11) NOT NULL ," +
		// 		" 'from' char(11) NOT NULL ," +
		// 		" 'to' char(11) NOT NULL ," +
		// 		" 'createTime' bigint NOT NULL ," +
		// 		" 'msgType' SMALLINT NOT NULL ," +
		// 		" 'content' TEXT NOT NULL ," +
		// 		" 'extras' TEXT ," +
		// 		" 'isRead' TINYINT " +
		// 		" )",
		// 	fail: (e) => {console.log(e)}
		// });
		
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: " create virtual table if not exists togroupmsg using fts4(groupId, fromId, createTime, msgType,content, extras, isRead, tokenize = icu)",
			fail: (e) => {console.log(e)}
		});
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: " create table if not exists togroupmsg(" +
		// 		" 'id' INTEGER PRIMARY KEY AUTOINCREMENT ," +
		// 		" 'groupId' char(11) NOT NULL ," +
		// 		" 'from' char(11) NOT NULL ," +
		// 		" 'createTime' bigint NOT NULL ," +
		// 		" 'msgType' SMALLINT NOT NULL ," +
		// 		" 'content' TEXT NOT NULL ," +
		// 		" 'extras' TEXT ," +
		// 		" 'isRead' TINYINT " +
		// 		" )",
		// 	fail: (e) => {console.log(e)}
		// });
	},
	//添加记录
	addData(tableName, data, cb) {
        let mysql = `INSERT INTO ${tableName} VALUES ('${tableName=='togroupmsg'? data.groupId : data.sessionId}','${data.from || data.fromId}',${tableName=='togroupmsg'?'':data.to+','}${data.createTime},${data.msgType||0},'${data.content}','${data.extras || ''}',${data.isRead || 0})`;
        plus.sqlite.executeSql({
        	name: 'msgDB',
        	sql: mysql,
        	success: (e) => {cb(true)},
        	fail: (e) => {cb(e)}
        });
    },
	//添加多条，例如获取历史消息时,mdata.length必须大于0
	addmData(tableName, mdata, cb) {
		let mysql = `INSERT INTO ${tableName} VALUES `;
		for (let c = 0; c < mdata.length; c++) {
			let data = mdata[c]
			mysql = mysql + `('${tableName=='togroupmsg'? data.groupId : data.sessionId}','${data.from || data.fromId}',${tableName=='togroupmsg'?'':data.to+','}${data.createTime},${data.msgType||0},'${data.content}','${data.extras || ''}',${data.isRead || 0})`

			if(c < mdata.length-1){
				mysql = mysql + ","
			}
		}
	    plus.sqlite.executeSql({
	    	name: 'msgDB',
	    	sql: mysql,
	    	success: (e) => {cb(true)},
	    	fail: (e) => {cb(e)}
	    });
	},
	//删除所有
	//totmsg,tosmsg,topmsg,togroupmsg
	deleteDB() {
		// select name from sqlite_master
		plus.sqlite.selectSql({
			name: 'msgDB',
			sql: "select name from sqlite_master",
			success: function(e) {console.log(e)
				for(let i=0; i<e.length; i++){
					plus.sqlite.executeSql({
						name: 'msgDB',
						sql: `DROP TABLE ${e[i].name}`
					});
				}
			},
			fail: function(e) {console.log(e)}
		});
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: "DROP TABLE totmsg"
		// });
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: "DROP TABLE tosmsg"
		// });
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: "DROP TABLE topmsg"
		// });
		// plus.sqlite.executeSql({
		// 	name: 'msgDB',
		// 	sql: "DROP TABLE togroupmsg"
		// });
	},
	//删除某对(id为groupId或者sessionId)
	deleteData(tableName, idName, id, cb) {
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: `DELETE FROM ${tableName} WHERE ${idName} = '${id}'`,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	
	//删除某行数据
	deleteLine(tableName, idName, id, createTime, cb) {
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: `DELETE FROM ${tableName} WHERE ${idName} = '${id}' AND createTime = ${createTime}`,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	//修改阅读状态（主键id）
	isRead(tableName, data, cb){
		let idName = 'sessionId'
		let id = data.sessionId
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: `UPDATE ${tableName} SET isRead = 1 WHERE createTime = ${data.createTime} AND ${idName} = '${id}'`,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	//查询(id为groupId或者sessionId)分页
	getDataByPage(tableName, idName, id, page, pageSize, cb) {
		// console.log(215, tableName, idName , id)
		let mysql = `SELECT * FROM ${tableName} WHERE ${idName} = '${id}' ORDER BY createTime DESC LIMIT ${(page-1)*20},${pageSize}`;
		plus.sqlite.selectSql({
			name: 'msgDB',
			sql: mysql,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	selectByNewOne(tableName, idName, id, cb) {
		let mysql = `SELECT * FROM ${tableName} WHERE ${idName} = '${id}' ORDER BY createTime DESC LIMIT 0,1`;
		plus.sqlite.selectSql({
			name: 'msgDB',
			sql: mysql,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	//根据关键词查询历史记录
	selectHistoryByKey(tableName, Key, page, pageSize, cb){
		// console.log(218, Key)
		let mysql = `SELECT docid, content, createTime, fromId FROM ${tableName} WHERE content MATCH '${Key}*' ORDER BY createTime DESC LIMIT ${(page-1)*20},${pageSize}`;
		plus.sqlite.selectSql({
			name: 'msgDB',
			sql: mysql,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	//修改图片链接
	update(tableName, data, cb){
		let idName = data.groupId?'groupId':'sessionId'
		let id = data.groupId?data.groupId:data.sessionId;
		let sql = ''
		if(data.content == ''){
			sql = `UPDATE ${tableName} SET content = '${data.content}' WHERE createTime = ${data.createTime} AND ${idName} = '${id}'`
		}else{
			sql = `UPDATE ${tableName} SET extras = '${data.extras}' WHERE createTime = ${data.createTime} AND ${idName} = '${id}'`
		}
		plus.sqlite.executeSql({
			name: 'msgDB',
			sql: sql,
			success: function(e) {cb(e)},
			fail: function(e) {cb(e)}
		});
	},
	
}
module.exports = myDB;